package com.realdolmen.jcc.mobileSquad.dao.impl
{
	import com.realdolmen.jcc.mobileSquad.dao.CustomerDao;
	import com.realdolmen.jcc.mobileSquad.domain.Address;
	import com.realdolmen.jcc.mobileSquad.domain.Contact;
	import com.realdolmen.jcc.mobileSquad.domain.Customer;
	
	import flash.data.SQLConnection;
	import flash.data.SQLResult;
	import flash.data.SQLStatement;
	import flash.desktop.NotificationType;
	import flash.filesystem.File;
	
	import mx.collections.ArrayCollection;
	
	import org.as3commons.lang.Assert;

	/**
	 * SQL lite implementation of the CustomerDao.
	 * @author DOMSIEBE
	 */	
	public class CustomerDaoImpl implements CustomerDao{
		private static const DATABASE_NAME:String 		= "customer.db";
		
		private static const CUSTOMER_TABLE_NAME:String = "customer";
		
		private static const ID:String 					= "id";
		//Company columns
		private static const COMPANY_NAME:String 		= "companyName";
		private static const COMPANY_STREET:String 		= "companyStreet";
		private static const COMPANY_CITY:String 		= "companyCity";
		private static const COMPANY_LATITUDE:String 	= "companyLatitude";
		private static const COMPANY_LONGITUDE:String 	= "companyLongitude";
		private static const COMPANY_HOME_PAGE:String 	= "companyHomePage";
		private static const COMPANY_LOGO:String 		= "companyLogo";
		//Contact columns
		private static const CONTACT_FIRST_NAME:String 	= "contactFirstName";
		private static const CONTACT_LAST_NAME:String 	= "contactLastName";
		private static const CONTACT_PHONE:String 		= "contactPhone";
		private static const CONTACT_EMAIL:String 		= "contactEmail";
		private static const CONTACT_PHOTO:String 		= "contactPhoto";
		
		private var sqlConnection:SQLConnection;
		
		/**
		 * Constructor of the CustomerDaoImpl.
		 * A sql connection will be created and opened. 
		 * The database name will be customer.db.
		 */		
		public function CustomerDaoImpl(){
			sqlConnection = new SQLConnection();
			sqlConnection.open(File.applicationStorageDirectory.resolvePath(DATABASE_NAME));
			dropCustomerTable();
			createCustomerTable();
		}
		
		/**
		 * Create the sql statement.
		 * @return A new sql statement.
		 */		
		private function createSQLStatement():SQLStatement{
			var sQLStatement:SQLStatement = new SQLStatement();
			sQLStatement.sqlConnection = sqlConnection;
			return sQLStatement;
		}
		
		/**
		 * Execute the sql statement.
		 * @param sQLStatement The sql statement.
		 * @param query The query to execute.
		 */		
		private function executeSQLStatement(sQLStatement:SQLStatement, query:String):void{
			sQLStatement.text = query;
			sQLStatement.execute();
		}
		
		/**
		 * Drop the customer table.
		 */		
		private function dropCustomerTable():void{
			executeSQLStatement(createSQLStatement(), "DROP TABLE IF EXISTS " + CUSTOMER_TABLE_NAME + ";");
		}
		
		/**
		 * Create the customers table.
		 * For the moment one customer can have one contact person and one address.
		 * Because of this restriction we do not need multiple tables.
		 */		
		private function createCustomerTable():void{
			var sql:String = 
				"CREATE TABLE IF NOT EXISTS " + CUSTOMER_TABLE_NAME + " (" + 
				"    " + ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 
				"    " + COMPANY_NAME + " TEXT, " +
				"    " + COMPANY_STREET + " TEXT, " +
				"    " + COMPANY_CITY + " TEXT, " +
				"    " + COMPANY_LONGITUDE + " TEXT, " +
				"    " + COMPANY_LATITUDE + " TEXT, " +
				"    " + COMPANY_HOME_PAGE + " TEXT, " +
				"    " + COMPANY_LOGO + " TEXT, " +
				"    " + CONTACT_FIRST_NAME + " TEXT, " +
				"    " + CONTACT_LAST_NAME + " TEXT, " +
				"    " + CONTACT_PHONE + " TEXT, " +
				"    " + CONTACT_EMAIL + " TEXT, " +
				"    " + CONTACT_PHOTO + " TEXT)"
				")";
			executeSQLStatement(createSQLStatement(), sql);
		}
		
		/**
		 * @InheriteDoc
		 */		
		public function populate():void {
			executeSQLStatement(createSQLStatement(), "delete FROM customer");
			//Customer 1
			var contact:Contact = new Contact("Alain", "Van Dam", "02 255 99 99", "info@dieteren.be", "contact/alain.jpg");
			var address:Address = new Address("Mechelsesteenweg 586/A", "Vilvoorde");
			var customer:Customer = new Customer(NaN, "D Ieteren", contact, address, "http://www.dieteren.com/Splash/en-en.aspx", "logo/dieteren.png");
			save(customer);
			//Customer 2
			contact = new Contact("Frankie", "Loosveld", "02 467 22 11", "info@corelio.be", "contact/frankie.jpg");
			address = new Address("Alfons Gossetlaan 30", "Groot-Bijgaarden");
			customer = new Customer(NaN, "Corelio", contact, address, "http://www.corelio.be/", "logo/corelio.png");
			save(customer);
			//Customer 3
			contact = new Contact("Guido", "Pallemans", "03 222 22 11", "info@nbb.be", "contact/guido.jpg");
			address = new Address("De Berlaimontlaan 14", "Brussel");
			customer = new Customer(NaN, "Nationale Bank", contact, address, "http://www.nbb.be/", "logo/nbb.png");
			save(customer);
			//Customer 4
			contact = new Contact("Michel", "Drets", "", "", "contact/michel.jpg");
			address = new Address("", "Brussel");
			customer = new Customer(NaN, "Audi", contact, address, "", "logo/audi.gif");
			save(customer);
			//Customer 5
			contact = new Contact("Harry", "Op den Bos", "", "", "contact/harry.jpg");
			address = new Address("", "Genk");
			customer = new Customer(NaN, "Nike", contact, address, "", "logo/nike.jpg");
			save(customer);
			//Customer 6
			contact = new Contact("Famke", "Janssen", "", "", "contact/janssen.jpg");
			address = new Address("", "Beerse");
			customer = new Customer(NaN, "Janssen", contact, address, "", "logo/janssen.gif");
			save(customer);
			//Customer 7
			contact = new Contact("Herman", "...", "", "", "contact/herman.jpg");
			address = new Address("", "Gent");
			customer = new Customer(NaN, "BMW", contact, address, "", "logo/bmw.jpg");
			save(customer);
		}
		
		/**
		 * @InheriteDoc
		 */		
		public function save(customer:Customer):void{
			Assert.notNull(customer, "Customer should not be null");
			Assert.notNull(customer.contact, "Customer contact should not be null");
			Assert.notNull(customer.address, "Customer address should not be null");
			var sQLStatement:SQLStatement = createSQLStatement();
			var query:String = "INSERT into " + CUSTOMER_TABLE_NAME + " (" +
				ID + ", " +
				COMPANY_NAME + ", " +
				COMPANY_STREET + ", " +
				COMPANY_CITY + ", " +
				COMPANY_LONGITUDE + ", " +
				COMPANY_LATITUDE + ", " +
				COMPANY_HOME_PAGE + ", " +
				COMPANY_LOGO + ", " +
				CONTACT_FIRST_NAME + ", " +
				CONTACT_LAST_NAME + ", " +
				CONTACT_PHONE + ", " +
				CONTACT_EMAIL + ", " +
				CONTACT_PHOTO + ")" +
				" values(" +
				":" + ID + ", " +
				":" + COMPANY_NAME + ", " +
				":" + COMPANY_STREET + ", " +
				":" + COMPANY_CITY + ", " +
				":" + COMPANY_LONGITUDE + ", " +
				":" + COMPANY_LATITUDE + ", " +
				":" + COMPANY_HOME_PAGE + ", " +
				":" + COMPANY_LOGO + ", " +
				":" + CONTACT_FIRST_NAME + ", " +
				":" + CONTACT_LAST_NAME + ", " +
				":" + CONTACT_PHONE + ", " +
				":" + CONTACT_EMAIL + ", " +
				":" + CONTACT_PHOTO + ")";
			sQLStatement.parameters[":" + ID + ""] = customer.id;
			sQLStatement.parameters[":" + COMPANY_NAME + ""] = customer.name;
			sQLStatement.parameters[":" + COMPANY_STREET + ""] = customer.address.street;
			sQLStatement.parameters[":" + COMPANY_CITY + ""] = customer.address.city;
			sQLStatement.parameters[":" + COMPANY_LONGITUDE + ""] = customer.address.longitude;
			sQLStatement.parameters[":" + COMPANY_LATITUDE + ""] = customer.address.latitude;
			sQLStatement.parameters[":" + COMPANY_HOME_PAGE + ""] = customer.homePage;
			sQLStatement.parameters[":" + COMPANY_LOGO + ""] = customer.logo;
			sQLStatement.parameters[":" + CONTACT_FIRST_NAME + ""] = customer.contact.firstName;
			sQLStatement.parameters[":" + CONTACT_LAST_NAME + ""] = customer.contact.lastName;
			sQLStatement.parameters[":" + CONTACT_PHONE + ""] = customer.contact.phone;
			sQLStatement.parameters[":" + CONTACT_EMAIL + ""] = customer.contact.email;
			sQLStatement.parameters[":" + CONTACT_PHOTO + ""] = customer.contact.photo;
			executeSQLStatement(sQLStatement, query);
		}
		
		/**
		 * @InheriteDoc
		 */		
		public function update(customer:Customer):void{
			Assert.notNull(customer, "Customer should not be null");
			remove(customer);
			customer.id = NaN;
			save(customer);
		}
		
		/**
		 * @InheriteDoc
		 */		
		public function remove(customer:Customer):void{
			var sQLStatement:SQLStatement = createSQLStatement();
			var query:String = "DELETE FROM " + CUSTOMER_TABLE_NAME + " where " + ID + " = :" + ID + ""
			sQLStatement.parameters[":" + ID + ""] = customer.id;
			executeSQLStatement(sQLStatement, query);
		}
		
		/**
		 * @InheriteDoc
		 */		
		public function findAll():ArrayCollection {
			var sQLStatement:SQLStatement = createSQLStatement();
			executeSQLStatement(sQLStatement, "SELECT * FROM " + CUSTOMER_TABLE_NAME + " order by " + COMPANY_NAME + ";");
			var customers:ArrayCollection = convertToCustomerList(sQLStatement.getResult());
			return customers;
		}
		
		/**
		 * @InheriteDoc
		 */		
		public function search(companyName:String):ArrayCollection {
			var sQLStatement:SQLStatement = createSQLStatement();
			executeSQLStatement(sQLStatement, "SELECT * FROM " + CUSTOMER_TABLE_NAME + " where " + COMPANY_NAME + " like '%" + companyName + "%' order by " + COMPANY_NAME + ";");
			var customers:ArrayCollection = convertToCustomerList(sQLStatement.getResult());
			return customers;
		}
		
		/**
		 * Convert the sql result to a list of customers.
		 * @param sQLResult The sql result
		 * @return A list of customers.
		 */		
		private function convertToCustomerList(sQLResult:SQLResult):ArrayCollection{
			Assert.notNull(sQLResult, "sQLResult should not be null");
			var customers:ArrayCollection = new ArrayCollection();
			if(!sQLResult.data){
				return customers;
			}else{
				for (var i:int = 0; i < sQLResult.data.length; i++){
					var customer:Customer = Customer.buildCustomer(sQLResult.data[i]);
					customers.addItem(customer);
				}
			}
			return customers;
		}
	}
}